# coding=utf-8
from openpyxl import Workbook, load_workbook


def get_column_by_title(path, col, start_row=1, sheet_name=None):
    """
    根据标题获取指定列
    :param path: excel路径
    :param col: 列名
    :param start_row: 起始行
    :param sheet_name: 工作表名，默认第一个
    :return:
    """
    wb = load_workbook(path)
    if sheet_name is None:
        ws = wb.active
    else:
        ws = wb[sheet_name]
    for cell in ws[start_row]: # 判断标题在标题列的列标，以便后面获取列值
        if col == cell.value:
            column_letter = cell.column_letter
            break
    else:
        raise Exception("第【{}】行不存在【{}】".format(start_row,col))
    data = ws[column_letter][start_row:] # 获取标题所在列，从起始行开始
    return [cell.value for cell in data] # 获取单元格对象的值

if __name__ == '__main__':
    print(get_column_by_title(r"../resource/测试2.xlsx","姓名"))
    print(get_column_by_title(r"../resource/测试1.xlsx","姓名",start_row=2))